package com.javasm.dao.MisMannageDao.impl;


import com.javasm.dao.MisMannageDao.SeriesDao;
import com.javasm.entity.MisMannageEntity.ProdSeriesInfo;
import com.javasm.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author:JAVASM
 * @description:
 * @date:2022/5/22 19:31
 * @version:0.1
 * @since:jdk11
 */
public class SeriesDaoImpl implements SeriesDao {

    @Override
    public String getCompanyById(Integer seriesId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select remit_money_company from tb_prod_series where series_id=?");

        String companyName = null;
        List<ProdSeriesInfo> prodSeriesInfoList = new ArrayList<ProdSeriesInfo>();
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,seriesId);
            rs = ps.executeQuery();

            while (rs.next()) {
                companyName = rs.getString("remit_money_company");

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
           JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return companyName;
    }

    @Override
    public List<ProdSeriesInfo> getSeriesName() {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select series_id,series_CHname from tb_prod_series");

        List<ProdSeriesInfo> prodSeriesInfoList = new ArrayList<ProdSeriesInfo>();
        try {
            ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();

            while (rs.next()) {
                Integer seriesId = rs.getInt("series_id");
                String seriesCHname = rs.getString("series_CHname");
                ProdSeriesInfo psi = new ProdSeriesInfo();
                psi.setSeriesId(seriesId);
                psi.setSeriesCHName(seriesCHname);
                prodSeriesInfoList.add(psi);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
           JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return prodSeriesInfoList;
    }

    @Override
    public String getSeriesNameById(Integer forProdSeriesId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select tps.series_CHname from tb_prod_series tps where tps.series_id=?");

        String seriesCHname = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,forProdSeriesId);
            rs = ps.executeQuery();

            while (rs.next()) {
                seriesCHname = rs.getString("series_CHname");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
           JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return seriesCHname;
    }

    @Override
    public Integer editProdSeries(ProdSeriesInfo editPsi) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;

        StringBuilder sb = new StringBuilder();
        sb.append("update tb_prod_series tps set tps.series_source=?,tps.series_CHname=?,tps.series_ENname=?,tps.series_type_id=? where tps.series_id=?");

        Integer editProdSeriesNum = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setString(1,editPsi.getSeriesSource());
            ps.setString(2,editPsi.getSeriesCHName());
            ps.setString(3,editPsi.getSeriesENName());
            ps.setInt(4,editPsi.getTypeId());
            ps.setInt(5,editPsi.getSeriesId());
            editProdSeriesNum = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, null);
        }
        return editProdSeriesNum;
    }

    @Override
    public ProdSeriesInfo queryProdSeriesById(Integer queryProdSeriesId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select tps.series_source,tps.series_CHname,tps.series_ENname,tt.type_name\n" +
                "from tb_prod_series tps,tb_type tt\n" +
                "where tps.series_type_id=tt.type_id\n" +
                "and tps.series_id=?");

        ProdSeriesInfo editForPsi = new ProdSeriesInfo();
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,queryProdSeriesId);
            rs = ps.executeQuery();
            while (rs.next()) {
                String seriesSource = rs.getString("series_source");
                String seriesCHname = rs.getString("series_CHname");
                String seriesENname = rs.getString("series_ENname");
                String secondTypeName = rs.getString("type_name");
                editForPsi.setSeriesSource(seriesSource);
                editForPsi.setSeriesCHName(seriesCHname);
                editForPsi.setSeriesENName(seriesENname);
                editForPsi.setSecondTypeName(secondTypeName);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return editForPsi;
    }

    @Override
    public Integer insertRemitMsg(ProdSeriesInfo addRemitMsgPsi, Integer addSeriesId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;

        StringBuilder sb = new StringBuilder();
        sb.append("update tb_prod_series set bank_name=?,swift=?,bank_code=?,cnaps=?,bank_addr=?,bank_city=?,payee_name=?,payee_account=?,payee_addr=?,remit_money_company=? where series_id=?");

        Integer addRemitMsgNum = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setString(1,addRemitMsgPsi.getBankName());
            ps.setString(2,addRemitMsgPsi.getSwift());
            ps.setString(3,addRemitMsgPsi.getBankCode());
            ps.setString(4,addRemitMsgPsi.getCnaps());
            ps.setString(5,addRemitMsgPsi.getBankAddr());
            ps.setString(6,addRemitMsgPsi.getBankCity());
            ps.setString(7,addRemitMsgPsi.getPayeeName());
            ps.setString(8,addRemitMsgPsi.getPayeeAccount());
            ps.setString(9,addRemitMsgPsi.getPayeeAddr());
            ps.setString(10,addRemitMsgPsi.getRemitMoneyCompany());
            ps.setInt(11,addRemitMsgPsi.getSeriesId());
            addRemitMsgNum = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, null);
        }
        return addRemitMsgNum;
    }

    @Override
    public Integer insertProdSeries(ProdSeriesInfo addPsi) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;

        StringBuilder sb = new StringBuilder();
        sb.append("insert into tb_prod_series(series_source,series_type_id,series_CHname,series_ENname) values (?,?,?,?)");

        Integer addSeriesNum = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setString(1,addPsi.getSeriesSource());
            ps.setInt(2,addPsi.getTypeId());
            ps.setString(3,addPsi.getSeriesCHName());
            ps.setString(4,addPsi.getSeriesENName());
            addSeriesNum = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, null);
        }
        return addSeriesNum;
    }

    @Override
    public Integer seriesTotalNum(String querySeriesName) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select count(1) as seriesTotalNum from tb_prod_series ");

        if (querySeriesName != null && !"".equals(querySeriesName)){
            sb.append("where series_CHname like '%"+querySeriesName+"%' or series_ENname like '%"+querySeriesName+"%'");
        }

        Integer seriesTotalNum = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                seriesTotalNum = rs.getInt("seriesTotalNum");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return seriesTotalNum;
    }

    @Override
    public List<ProdSeriesInfo> getSeriesByPage(Integer page, Integer pageSize, String querySeriesName) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select tps.series_id,tps.series_CHname,tps.series_ENname,tps.remit_money_company from tb_prod_series tps ");

        if (querySeriesName != null && !"".equals(querySeriesName)){
            sb.append("where tps.series_CHname like '%"+querySeriesName+"%' or tps.series_ENname like '%"+querySeriesName+"%'");
        }

        sb.append("limit "+(page-1)*pageSize+","+pageSize);
        List<ProdSeriesInfo> psiList = new ArrayList<ProdSeriesInfo>();
        try {
            ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer seriesId = rs.getInt("series_id");
                String seriesCHname = rs.getString("series_CHname");
                String seriesENname = rs.getString("series_ENname");
                String remitMoneyCompany = rs.getString("remit_money_company");
                ProdSeriesInfo psi = new ProdSeriesInfo();
                psi.setSeriesId(seriesId);
                psi.setSeriesCHName(seriesCHname);
                psi.setSeriesENName(seriesENname);
                psi.setRemitMoneyCompany(remitMoneyCompany);
                psiList.add(psi);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return psiList;
    }
}
